﻿using HCQ2_Common.SQL;
using HCQ2_IDAL;
using HCQ2_Model.VRDD.InputDto._0602;
using HCQ2_Model.VRDD.InputDto._0730;
using HCQ2_Model.VRDD.InputDto._0806;
using HCQ2_Model.VRDD.InputDto._0818;
using HCQ2_Model.VRDD.OutputDto._0602;
using HCQ2_Model.VRDD.OutputDto._0730;
using HCQ2_Model.VRDD.OutputDto._0806;
using HCQ2_Model.VRDD.OutputDto._0818;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_VRDDScanLoginDAL: IT_VRDDScanLoginDAL
    {
        /// <summary>
        ///  获取用户的扫码登录列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<OutputUserScan> GetUserScanLoginList(InputUserScan param)
        {
            sb?.Clear();
            sb.Append($@"select device_id,device_name,cast(user_status as int)as user_status,CONVERT(varchar(100),create_date,120) as create_date,
           (case when user_status>0 then '' else CONVERT(varchar(100),update_date,120) end) as update_date from T_VRDDScanLogin where userid='{param.userid}' ");
            if (!string.IsNullOrEmpty(param.device_id))
                sb.Append($" and device_id='{param.device_id}' ");
            if (param.user_status > -1)
                sb.Append($" and user_status={param.user_status} ");
            sb.Append(" order by user_status,create_date;");
            return SqlHelper.ExecuteList<OutputUserScan>(sb.ToString());
        }

        /// <summary>
        ///  获取用户体验主表记录
        /// </summary>
        /// <param name="user_id">用户id</param>
        /// <param name="file_id">作品id</param>
        /// <returns></returns>
        public VRRecordMainResult GetRecordMainResult(int user_id,int file_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT folder.folder_id,folder.folder_name,rec.file_id,info.file_name,info.file_note,rec.trial_num,rec.em_time,
            rec.total_score,m.result_ok,m.result_error FROM (SELECT TOP 1 user_id,file_id,COUNT(0)as trial_num,SUM(score)as total_score,
             SUM(em_time)AS em_time FROM T_VLionsRecord WHERE user_id={user_id} ");
            if (file_id > 0)
                sb.Append($" AND file_id={file_id} ");
            sb.Append($@"GROUP BY user_id,file_id)rec INNER JOIN
             (SELECT file_id,folder_id FROM dbo.T_DocumentFolderRelation)rel ON rel.file_id = rec.file_id INNER JOIN
             (SELECT folder_id,folder_name FROM dbo.T_DocumentFolder)folder ON rel.folder_id=folder.folder_id INNER JOIN
             (SELECT file_id,file_name,file_note FROM dbo.T_DocumentInfo)info ON info.file_id=rec.file_id INNER JOIN 
             (SELECT TOP 1 user_id,file_id,ISNULL(SUM(result_ok),0)AS result_ok,ISNULL(SUM(result_error),0)AS result_error 
             FROM dbo.T_VRExperienceRecordMain WHERE user_id={user_id} ");
            if (file_id > 0)
                sb.Append($" AND file_id={file_id} ");
            sb.Append("GROUP BY user_id,file_id)m ON rec.user_id=m.user_id;");
            return SqlHelper.ExecuteList<VRRecordMainResult>(sb.ToString())?.FirstOrDefault();
        }
        /// <summary>
        ///  获取体验记录列表
        /// </summary>
        /// <param name="user_id">用户id</param>
        /// <param name="file_id">作品id</param>
        /// <returns></returns>
        public List<VRRecordResult> GetRecordResult(int user_id, int file_id)
        {
            sb?.Clear();
            sb.Append($@" SELECT er_id,CONVERT(varchar(100),in_date,120)AS in_date,CONVERT(varchar(100),out_date,120)AS out_date,
            em_time FROM dbo.T_VLionsRecord WHERE user_id={user_id} AND file_id={file_id};");
            return SqlHelper.ExecuteList<VRRecordResult>(sb.ToString());
        }

        /// <summary>
        ///  获取成绩列表
        /// </summary>
        /// <param name="er_id"></param>
        /// <returns></returns>
        public List<VRRecordScoreResult> GetRecordScoreResult(int er_id)
        {
            sb?.Clear();
            sb.Append($@" SELECT r.folder_code,r.folder_name,d.topic_num,ISNULL(d.score,0)AS score FROM 
             (SELECT er_id,folder_code,folder_name FROM dbo.T_VLionsRecord WHERE er_id={er_id})r INNER JOIN
             (SELECT er_id,topic_num,SUM(score)AS score FROM dbo.T_VLinesExperienceDetail WHERE er_id={er_id} 
             GROUP BY er_id,topic_num)d ON d.er_id = r.er_id;");
            return SqlHelper.ExecuteList<VRRecordScoreResult>(sb.ToString());
        }

        /// <summary>
        ///  获取答题列表
        /// </summary>
        /// <param name="user_id">用户id</param>
        /// <param name="er_id">体验记录id</param>
        /// <param name="topic_num">题号</param>
        /// <returns></returns>
        public List<VRRecordAnswerResult> GetRecordAnswerResult(int user_id, int er_id, string topic_num)
        {
            sb?.Clear();
            sb.Append($@"SELECT num,d.status,d.score,m.content,m.answer,d.sel_result FROM 
            (SELECT wm_id,status,(CASE WHEN status<1 THEN 0 ELSE score END)AS score,sel_result FROM 
            dbo.T_VLinesExperienceDetail WHERE user_id={user_id} AND er_id={er_id} ");
            if (!string.IsNullOrEmpty(topic_num))
                sb.Append($" AND topic_num='{topic_num}' ");
            sb.Append(@")d INNER JOIN
            (SELECT wm_id,num,content,answer FROM dbo.T_VRExperienceWorkMain)m ON m.wm_id = d.wm_id;");
            return SqlHelper.ExecuteList<VRRecordAnswerResult>(sb.ToString());
        }
        /// <summary>
        ///  获取体验记录
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<RecordCourse> GetRecordCourse(InputVRWork param)
        {
            sb?.Clear();
            sb.Append($@"SELECT f.folder_id,f.folder_name,f.folder_note,f.folder_url,folder_path,
            work_num=(SELECT COUNT(0) FROM dbo.T_DocumentInfo WHERE file_status=5 AND if_remove=0 AND file_id IN(
                SELECT file_id FROM dbo.T_DocumentFolderRelation WHERE folder_path LIKE f.folder_path+'%')),
            record_work_num=(SELECT COUNT(DISTINCT file_id) FROM dbo.T_VLionsRecord WHERE file_id IN(
                SELECT file_id FROM dbo.T_DocumentFolderRelation WHERE folder_path LIKE f.folder_path+'%')),	
            record_num=(SELECT COUNT(0) FROM dbo.T_VLionsRecord WHERE file_id IN(
                SELECT file_id FROM dbo.T_DocumentFolderRelation WHERE folder_path LIKE f.folder_path+'%')),
            record_time=(SELECT SUM(em_time) FROM dbo.T_VLionsRecord WHERE file_id IN(
                SELECT file_id FROM dbo.T_DocumentFolderRelation WHERE folder_path LIKE f.folder_path+'%')),
            ok_num=(SELECT SUM(result_ok)AS result_ok FROM T_VRExperienceWorkMain WHERE folder_path LIKE f.folder_path+'%'),
            error_num=(SELECT SUM(result_error)AS result_ok FROM T_VRExperienceWorkMain WHERE folder_path LIKE f.folder_path+'%')
             FROM dbo.T_DocumentFolder f WHERE folder_path = '{param.folder_path}' ");
            return SqlHelper.ExecuteList<RecordCourse>(sb.ToString());
        }
        /// <summary>
        ///  获取成绩列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<RecordScoreListResult> GetRecordCourse(string folder_path,string keyword)
        {
            sb?.Clear();
            sb.Append($@"SELECT info.file_id,info.file_name,info.file_note,folder.folder_id,folder.folder_name,cd.in_date,info.trial_num,
            info.em_time,info.total_score,ISNULL(m.result_ok,0)AS result_ok,ISNULL(m.result_error,0)AS result_error FROM 
            (SELECT file_id,file_name,file_note=(SELECT file_note FROM dbo.T_DocumentInfo WHERE file_id=r.file_id),
            COUNT(0)AS trial_num,SUM(r.em_time)AS em_time,total_score=SUM(r.score)
             FROM T_VLionsRecord r WHERE file_id IN(
	            SELECT file_id FROM dbo.T_DocumentFolderRelation WHERE folder_path LIKE '{folder_path}%')
	            GROUP BY file_id,file_name)info INNER JOIN
            (SELECT file_id,folder_id FROM dbo.T_DocumentFolderRelation)rel ON rel.file_id = info.file_id INNER JOIN
            (SELECT folder_id,folder_name FROM dbo.T_DocumentFolder)folder ON rel.folder_id=folder.folder_id INNER JOIN 
            (SELECT file_id,ISNULL(SUM(result_ok),0)AS result_ok,ISNULL(SUM(result_error),0)AS result_error 
             FROM dbo.T_VRExperienceRecordMain GROUP BY file_id)m ON m.file_id = info.file_id INNER JOIN
            (SELECT rj.file_id,CONVERT(varchar(100),in_date,120)AS in_date FROM 
             (SELECT ROW_NUMBER() OVER(PARTITION BY file_id ORDER BY in_date DESC)AS num,file_id,in_date FROM 	T_VLionsRecord WHERE file_id IN(
	            SELECT file_id FROM dbo.T_DocumentFolderRelation WHERE folder_path LIKE '{folder_path}%'))rj WHERE rj.num=1)cd ON cd.file_id = info.file_id ");
            if (!string.IsNullOrEmpty(keyword))
                sb.Append($" AND (info.file_name LIKE '%{keyword}%' OR info.file_note LIKE '%{keyword}%') ");
            return SqlHelper.ExecuteList<RecordScoreListResult>(sb.ToString());
        }

        /// <summary>
        ///  获取服务类别信息
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>

        public List<OutputService> GetServiceType(InputService param)
        {
            sb?.Clear();
            sb.Append($@"SELECT s_type,s_num,s_top FROM dbo.T_VRDDCircleBaseService where ISNULL(s_type,'')<>'' ");
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND s_type LIKE '%{param.keyword}%' ");
            sb.Append(" order by s_top desc,s_num desc,update_date desc;");
            return SqlHelper.ExecuteList<OutputService>(sb.ToString());
        }
        /// <summary>
        /// 获取店铺目录列表
        /// </summary>
        /// <param name="param"></param>
        /// <param name="user_id"></param>
        /// <returns></returns>
        public List<OutputStorePage> GetStorePageList(InputStorePage param,int user_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT * FROM (SELECT folder_id AS ID,folder_name AS Name,folder_path AS Path,folder_note AS Note,
            folder_url AS Icon,is_recommend AS isPush,focus_num AS focusNum,star_num AS starNum,
            isFocus=(SELECT CAST(COUNT(0) AS BIT) FROM dbo.T_VRUserFolder WHERE folder_id=f.folder_id AND user_guid='{param.userid}'),
            isStar=(SELECT CAST(COUNT(0) AS BIT) FROM T_VLineUserFolderStar WHERE folder_id=f.folder_id AND user_guid='{param.userid}'),
            isPartner=(SELECT CAST(COUNT(0) AS BIT) FROM dbo.T_VRFounderApply WHERE folder_id=f.folder_id AND user_id={user_id}),
            isFounder=(CASE WHEN founder_id={user_id} THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END),
            founder_id AS founderID,founder_name AS founderName,
            founderPhone=(SELECT user_phone FROM dbo.T_User WHERE user_id=founder_id),City,
            dealNote=(SELECT TOP 1 deal_note FROM dbo.T_VRFounderDeal WHERE folder_id=f.folder_id),
            dealId=(SELECT TOP 1 id FROM dbo.T_VRFounderDeal WHERE folder_id=f.folder_id),
            Distance=dbo.GetDistance({param.gpsX},{param.gpsY},gps_x,gps_y),gps_x,gps_y,update_date
            FROM dbo.T_DocumentFolder f WHERE if_sys=1 AND group_name='store' AND page_type='DDCircle' ");
            //关键字
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND (folder_name LIKE '%{param.keyword}%' OR folder_note LIKE '%{param.keyword}%') ");
            //已收藏
            if (param.focus > -1)
                sb.Append($" AND folder_id IN(SELECT folder_id FROM T_VRUserFolder WHERE user_guid='{param.userid}') ");
            //已推荐
            if (param.push > -1)
                sb.Append($" AND is_recommend=1 ");
            //服务类别
            if (!string.IsNullOrEmpty(param.bsType))
                sb.Append($" AND bs_type LIKE '%{param.bsType}%' ");
            if(param.order_type>1)
                sb.Append($")m ORDER BY m.Distance ASC;");
            else
                sb.Append($")m ORDER BY m.update_date DESC;");
            return SqlHelper.ExecuteList<OutputStorePage>(sb.ToString());
        }
    }
}